﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EFong.Data;
using EFong.BL.Const;

namespace EFong.BL
{
    public class ReportBL
    {
        private EFongEntities entities = EFongEntitiesManager.GetInstance();

        public List<Contract.银行对账单> GetReport01(Contract.BankAccount bankAccount, DateTime startDate, DateTime endDate)
        {
            //if (bankAccount == null)
            //    throw new BusinessException(VerificationText.请输入银行账户);

            DateTime startDateTime = new DateTime(startDate.Year, startDate.Month, startDate.Day);
            DateTime endDateTime = new DateTime(endDate.Year, endDate.Month, endDate.Day);

            List<Contract.银行对账单> resultList = new List<Contract.银行对账单>();
            var list1 = (from cs in entities.CustomerSettlement
                         where cs.SettlementDate >= startDateTime &&
                               cs.SettlementDate <= endDateTime
                         orderby cs.SettlementDate, cs.Ref
                         select new Contract.银行对账单()
                         {
                             单据号 = cs.Ref.Trim(),
                             银行ID = cs.BankAccountID.Value,
                             银行 = cs.BankAccount.Name.Trim(),
                             单据类型 = cs.Type.Trim(),
                             日期 = cs.SettlementDate,
                             客户 = (from c in entities.Customer
                                   where c.ID == cs.CustomerID
                                   select c.Name.Trim()).FirstOrDefault(),
                             支出收入 = cs.Amount,
                             备注 = cs.Remark
                         }).ToList();
            resultList.AddRange(list1);

            var list2 = (from vs in entities.VendorSettlement
                         where vs.PostingDate >= startDateTime &&
                               vs.PostingDate <= endDateTime
                         orderby vs.Ref
                         select new Contract.银行对账单()
                         {
                             单据号 = vs.Ref.Trim(),
                             银行ID = vs.BankAccountID.Value,
                             银行 = vs.BankAccount.Name.Trim(),
                             单据类型 = vs.Type.Trim(),
                             日期 = vs.PostingDate,
                             供应商 = (from v in entities.Vendor
                                    where v.ID == vs.VendorID
                                    select v.Name.Trim()).FirstOrDefault(),
                             支出收入 = 0 - vs.Amount,
                             备注 = vs.Remark
                         }).ToList();
            resultList.AddRange(list2);

            var list3 = (from vs in entities.OtherPayment
                         where vs.Date >= startDateTime &&
                               vs.Date <= endDateTime
                         select new Contract.银行对账单()
                         {
                             单据号 = vs.Content.Trim(),
                             银行ID = vs.BankAccountID == null ? Guid.Empty : vs.BankAccountID.Value,
                             银行 = vs.BankAccountID == null ? string.Empty : vs.BankAccount.Name.Trim(),
                             单据类型 = vs.Type.Trim(),
                             日期 = vs.Date,
                             供应商 = string.Empty,
                             支出收入 = 0 - vs.Amount,
                             备注 = vs.Remark
                         }).ToList();
            resultList.AddRange(list3);

            var list4 = (from vs in entities.AmountTransfer
                         where vs.TransferDate >= startDateTime &&
                               vs.TransferDate <= endDateTime
                         select new Contract.银行对账单()
                         {
                             单据号 = "至" + vs.BankAccount1.Name.Trim(),
                             银行ID = vs.SourceID,
                             银行 = vs.BankAccount1.Name.Trim(),
                             单据类型 = "转账出",
                             日期 = vs.TransferDate,
                             供应商 = string.Empty,
                             支出收入 = 0 - vs.Amount,
                             备注 = string.Empty
                         }).ToList();
            resultList.AddRange(list4);

            var list5 = (from vs in entities.AmountTransfer
                         where vs.ReceiveDate >= startDateTime &&
                               vs.ReceiveDate <= endDateTime
                         select new Contract.银行对账单()
                         {
                             单据号 = "从" + vs.BankAccount.Name.Trim(),
                             银行ID = vs.TargetID.Value,
                             银行 = vs.BankAccount.Name.Trim(),
                             单据类型 = "转账入",
                             日期 = vs.ReceiveDate,
                             供应商 = string.Empty,
                             支出收入 = vs.Amount,
                             备注 = string.Empty
                         }).ToList();
            resultList.AddRange(list5);

            if (bankAccount != null)
                resultList = (from c in resultList
                              where c.银行ID == bankAccount.ID
                              orderby c.日期, c.银行
                              select c).ToList();
            else
                resultList = (from c in resultList
                              orderby c.日期, c.银行
                              select c).ToList();

            return resultList;
        }

        public List<Contract.银行对账单> GetReport011(DateTime startDate, DateTime endDate)
        {
            DateTime startDateTime = new DateTime(startDate.Year, startDate.Month, startDate.Day);
            DateTime endDateTime = new DateTime(endDate.Year, endDate.Month, endDate.Day);

            List<Contract.银行对账单> resultList = new List<Contract.银行对账单>();
            var list1 = (from cs in entities.CustomerSettlement
                         where cs.SettlementDate >= startDateTime &&
                               cs.SettlementDate <= endDateTime &&
                               cs.BankAccountID == null
                         orderby cs.SettlementDate, cs.Ref
                         select new Contract.银行对账单()
                         {
                             单据号 = cs.Ref.Trim(),
                             单据类型 = cs.Type.Trim(),
                             日期 = cs.SettlementDate,
                             客户 = (from c in entities.Customer
                                   where c.ID == cs.CustomerID
                                   select c.Name.Trim()).FirstOrDefault(),
                             支出收入 = cs.Amount,
                             备注 = cs.Remark
                         }).ToList();
            resultList.AddRange(list1);

            var list2 = (from vs in entities.VendorSettlement
                         where vs.PostingDate >= startDateTime &&
                               vs.PostingDate <= endDateTime &&
                               vs.BankAccountID == null
                         orderby vs.Ref
                         select new Contract.银行对账单()
                         {
                             单据号 = vs.Ref.Trim(),
                             单据类型 = vs.Type.Trim(),
                             日期 = vs.PostingDate,
                             供应商 = (from v in entities.Vendor
                                    where v.ID == vs.VendorID
                                    select v.Name.Trim()).FirstOrDefault(),
                             支出收入 = 0 - vs.Amount,
                             备注 = vs.Remark
                         }).ToList();
            resultList.AddRange(list2);

            var list3 = (from vs in entities.OtherPayment
                         where vs.Date >= startDateTime &&
                               vs.Date <= endDateTime &&
                               vs.BankAccountID == null
                         select new Contract.银行对账单()
                         {
                             单据号 = vs.Content.Trim(),
                             单据类型 = vs.Type.Trim(),
                             日期 = vs.Date,
                             供应商 = string.Empty,
                             支出收入 = 0 - vs.Amount,
                             备注 = vs.Remark
                         }).ToList();
            resultList.AddRange(list3);

            return (from c in resultList
                    orderby c.日期
                    select c).ToList();
        }

        public List<Contract.供应商对账单> GetReport02(Contract.RVendor vendor, DateTime month)
        {
            if (vendor == null)
                throw new BusinessException(VerificationText.请输入供应商);

            DateTime startDateTime = new DateTime(month.Year, month.Month, 1);
            DateTime endDateTime = startDateTime.AddMonths(1).AddMilliseconds(-5);

            var list = (from vs in entities.VendorSettlement
                        where vs.VendorID == vendor.ID &&
                              vs.PostingDate >= startDateTime &&
                              vs.PostingDate <= endDateTime
                        //&&
                        //(vs.PayType != "现金" && vs.BankAccountID != null)
                        orderby vs.PostingDate, vs.Ref
                        select new Contract.供应商对账单()
                        {
                            单据号 = vs.Ref.Trim(),
                            单据类型 = vs.Type.Trim(),
                            过账日期 = vs.PostingDate,
                            Vendor = vendor.Name,
                            收入支出 = vs.Amount,
                            备注 = vs.Remark
                        }).ToList();

            return list;
        }

        public List<Contract.固定费用清单> GetReport03(DateTime month)
        {
            DateTime startDateTime = new DateTime(month.Year, month.Month, 1);
            DateTime endDateTime = startDateTime.AddMonths(1).AddMilliseconds(-5);

            var list = (from op in entities.OtherPayment
                        where op.Type.Trim() == "固定支出" &&
                              op.Date >= startDateTime &&
                              op.Date <= endDateTime
                        orderby op.Date
                        select new Contract.固定费用清单()
                        {
                            内容 = op.Content.Trim(),
                            日期 = op.Date,
                            金额 = op.Amount,
                            备注 = op.Remark.Trim()
                        }).ToList();

            return list;
        }

        public List<Contract.零散费用清单> GetReport04(DateTime month)
        {
            DateTime startDateTime = new DateTime(month.Year, month.Month, 1);
            DateTime endDateTime = startDateTime.AddMonths(1).AddMilliseconds(-5);

            var list = (from op in entities.OtherPayment
                        where op.Type.Trim() == "零散支出" &&
                              op.Date >= startDateTime &&
                              op.Date <= endDateTime
                        orderby op.Date
                        select new Contract.零散费用清单()
                        {
                            内容 = op.Content.Trim(),
                            日期 = op.Date,
                            金额 = op.Amount,
                            备注 = op.Remark.Trim()
                        }).ToList();

            return list;
        }

        public List<Contract.利润表> GetReport05(DateTime startDay, DateTime endDay)
        {
            DateTime startDateTime = new DateTime(startDay.Year, startDay.Month, startDay.Day);
            DateTime endDateTime = (new DateTime(endDay.Year, endDay.Month, endDay.Day)).AddDays(1).AddMilliseconds(-5);

            List<Contract.利润表> resultList = new List<Contract.利润表>();

            var list1 = (from fo in entities.FlightOrder
                         where fo.OrderDate >= startDateTime &&
                               fo.OrderDate <= endDateTime &&
                               fo.Status != "已作废"
                         select new Contract.利润表()
                         {
                             单据号 = fo.Ref.Trim(),
                             单据类型 = "飞机票",
                             日期 = fo.OrderDate,
                             利润 = fo.TotalProfit,
                             备注 = fo.Remark,
                             客户 = fo.Customer.Name.Trim(),
                             结算方式 = fo.CreditTerm.Trim(),
                             积分 = fo.TotalIntergral,
                             已收款 = (from cs in entities.CustomerSettlement
                                    where cs.SourceID == fo.ID
                                    select cs).Count() > 0
                         }).ToList();
            resultList.AddRange(list1);

            var list2 = (from tto in entities.TrainTicketOrder
                         where tto.OrderDate >= startDateTime &&
                               tto.OrderDate <= endDateTime &&
                               tto.Status != "已作废"
                         select new Contract.利润表()
                         {
                             单据号 = tto.Ref.Trim(),
                             单据类型 = "火车票",
                             日期 = tto.OrderDate,
                             利润 = tto.TotalProfit,
                             备注 = tto.Remark,
                             客户 = tto.Customer.Name.Trim(),
                             结算方式 = tto.CreditTerm.Trim(),
                             已收款 = (from cs in entities.CustomerSettlement
                                    where cs.SourceID == tto.ID
                                    select cs).Count() > 0
                         }).ToList();
            resultList.AddRange(list2);

            var list3 = (from hb in entities.HotelBooking
                         where hb.OrderDate >= startDateTime &&
                               hb.OrderDate <= endDateTime &&
                               hb.Status != "已作废"
                         select new Contract.利润表()
                         {
                             单据号 = hb.Ref.Trim(),
                             单据类型 = "酒店",
                             日期 = hb.OrderDate,
                             利润 = hb.TotalProfit,
                             备注 = hb.Remark,
                             客户 = hb.Customer.Name.Trim(),
                             结算方式 = hb.PaymentType.Trim(),
                             积分 = hb.Intergral,
                             已收款 = (from cs in entities.CustomerSettlement
                                    where cs.SourceID == hb.ID
                                    select cs).Count() > 0
                         }).ToList();
            resultList.AddRange(list3);

            var list4 = (from ob in entities.OtherBusiness
                         where ob.OrderDate >= startDateTime &&
                               ob.OrderDate <= endDateTime &&
                               ob.Status != "已作废"
                         select new Contract.利润表()
                         {
                             单据号 = ob.Ref.Trim(),
                             单据类型 = "其他",
                             日期 = ob.OrderDate,
                             利润 = ob.TotalProfit,
                             备注 = ob.Remark,
                             客户 = ob.Customer.Name.Trim(),
                             结算方式 = ob.CreditTerm.Trim(),
                             积分 = ob.Intergral,
                             已收款 = (from cs in entities.CustomerSettlement
                                    where cs.SourceID == ob.ID
                                    select cs).Count() > 0
                         }).ToList();
            resultList.AddRange(list4);

            var list5 = (from ob in entities.TouringParty
                         where ob.OrderDate >= startDateTime &&
                               ob.OrderDate <= endDateTime &&
                               ob.Status != "已作废"
                         select new Contract.利润表()
                         {
                             单据号 = ob.Ref.Trim(),
                             单据类型 = "自助团",
                             日期 = ob.OrderDate,
                             利润 = ob.TotalPrice - ob.TotalVendorPrice,
                             备注 = ob.Remark,
                             客户 = ob.Customer.Name.Trim(),
                             已收款 = (from cs in entities.CustomerSettlement
                                    where cs.SourceID == ob.ID
                                    select cs).Count() > 0
                         }).ToList();
            resultList.AddRange(list5);

            //var list5 = (from fc in entities.FlightChange
            //             where fc.OrderDate >= startDateTime &&
            //                   fc.OrderDate <= endDateTime
            //             select new Contract.利润表()
            //             {
            //                 单据号 = fc.Ref.Trim(),
            //                 单据类型 = "飞机票改票",
            //                 日期 = fc.OrderDate,
            //                 利润 = fc.Profit,
            //                 备注 = fc.Remark
            //             }).ToList();
            //resultList.AddRange(list5);

            //var list6 = (from fr in entities.FlightReturn
            //             where fr.OrderDate >= startDateTime &&
            //                   fr.OrderDate <= endDateTime
            //             select new Contract.利润表()
            //             {
            //                 单据号 = fr.Ref.Trim(),
            //                 单据类型 = "飞机票退票",
            //                 日期 = fr.OrderDate,
            //                 利润 = fr.Profit,
            //                 备注 = fr.Remark
            //             }).ToList();
            //resultList.AddRange(list6);

            //var list7 = (from ttr in entities.TrainTicketReturn
            //             where ttr.OrderDate >= startDateTime &&
            //                   ttr.OrderDate <= endDateTime
            //             select new Contract.利润表()
            //             {
            //                 单据号 = ttr.Ref.Trim(),
            //                 单据类型 = "飞机票",
            //                 日期 = ttr.OrderDate,
            //                 利润 = ttr.Profit,
            //                 备注 = string.Empty
            //             }).ToList();
            //resultList.AddRange(list7);

            var list8 = (from vs in entities.OtherPayment
                         where vs.Date >= startDateTime &&
                               vs.Date <= endDateTime
                         select new Contract.利润表()
                         {
                             单据号 = vs.Content.Trim(),
                             单据类型 = vs.Type.Trim(),
                             日期 = vs.Date,
                             利润 = 0 - vs.Amount,
                             备注 = vs.Remark,
                             已收款 = true,
                         }).ToList();
            resultList.AddRange(list8);


            return (from p in resultList
                    orderby p.日期, p.单据号
                    select p).ToList();
        }

        //private object test()
        //{
        //    return GetReport06(new Contract.RCustomer(), DateTime.Now);
        //}

        public List<Contract.飞机票月结对账单> GetReport06(Contract.RCustomer customer, DateTime startDate ,DateTime endDate)
        {
            if (customer == null)
                throw new BusinessException(VerificationText.请输入客户);
            DateTime startDateTime = new DateTime(startDate.Year, startDate.Month, startDate.Day);
            DateTime endDateTime = new DateTime(endDate.Year, endDate.Month, endDate.Day);

            List<Contract.飞机票月结对账单> list = new List<Contract.飞机票月结对账单>();

            var list1 = (from fod in entities.FlightOrderDetail
                         join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                         where fo.CustomerID == customer.ID &&
                               fo.OrderDate >= startDateTime &&
                               fo.OrderDate <= endDateTime &&
                               fo.Status != "已作废"
                         orderby fo.OrderDate, fo.Ref, fod.Seq
                         select new Contract.飞机票月结对账单()
                         {
                             单据号 = fo.Ref.Trim(),
                             出票日期 = fo.ProvideTicketDate,
                             TempID = fod.ID,
                             积分 = fod.Intergral,
                             金额 = fod.AccountReceivable,
                             乘客CName = fod.Passenger.CName.Trim(),
                             乘客EName = fod.Passenger.EName.Trim(),
                             Tax1 = fod.Tax1,
                             Tax2 = fod.Tax2,
                             机票价格 = fod.Price,
                             结算方式 = fo.CreditTerm
                         }).ToList();

            list.AddRange(list1);


            var list2 = (from fc in entities.FlightChange
                         join fod in entities.FlightOrderDetail on fc.FlightOrderDetailID equals fod.ID
                         join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                         where fo.CustomerID == customer.ID &&
                               fc.OrderDate >= startDateTime &&
                               fc.OrderDate <= endDateTime &&
                               fo.Status != "已作废"
                         orderby fo.OrderDate, fo.Ref, fod.Seq
                         select new Contract.飞机票月结对账单()
                         {
                             单据号 = fc.Ref.Trim(),
                             出票日期 = fo.ProvideTicketDate,
                             TempID = fod.ID,
                             积分 = 0,
                             金额 = fc.Price,
                             乘客CName = fod.Passenger.CName.Trim(),
                             乘客EName = fod.Passenger.EName.Trim(),
                             机票价格 = fc.Price,
                             结算方式 = fo.CreditTerm
                         }).ToList();

            list.AddRange(list2);

            var list3 = (from fr in entities.FlightReturn
                         join fod in entities.FlightOrderDetail on fr.FlightOrderDetailID equals fod.ID
                         join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                         where fo.CustomerID == customer.ID &&
                               fr.OrderDate >= startDateTime &&
                               fr.OrderDate <= endDateTime &&
                               fo.Status != "已作废"
                         orderby fo.OrderDate, fo.Ref, fod.Seq
                         select new Contract.飞机票月结对账单()
                         {
                             单据号 = fr.Ref.Trim(),
                             出票日期 = fo.ProvideTicketDate,
                             TempID = fod.ID,
                             积分 = fr.Intergral,
                             金额 = 0 - fr.CustomerReturnAmount,
                             乘客CName = fod.Passenger.CName.Trim(),
                             乘客EName = fod.Passenger.EName.Trim(),
                             机票价格 = 0 - fr.CustomerReturnAmount,
                             结算方式 = fo.CreditTerm
                         }).ToList();

            list.AddRange(list3);

            foreach (Contract.飞机票月结对账单 _飞机票月结对账单 in list)
            {
                var tempList = (from fw in entities.FlightWay
                                where fw.FlightOrderDetailID == _飞机票月结对账单.TempID
                                orderby fw.Seq
                                select new
                                {
                                    Start = fw.DepartLocation,
                                    End = fw.Destination,
                                    Date = fw.DepartDate,
                                }).ToList();

                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < tempList.Count; i++)
                {
                    sb.Append(string.Format("{0} - ", tempList[i].Start));
                }
                sb.Append(tempList[tempList.Count - 1].End);

                _飞机票月结对账单.行程 = sb.ToString();
                _飞机票月结对账单.出发日期 = tempList[0].Date;
            }

            return (from f in list
                    orderby f.出票日期, f.单据号
                    select f).ToList();
        }

        public List<Contract.火车票月结对账单> GetReport07(Contract.RCustomer customer, DateTime month)
        {
            //if (customer == null)
            //    throw new BusinessException(VerificationText.请输入客户);

            DateTime startDateTime, endDateTime;
            if (month == DateTime.MinValue)
            {
                startDateTime = new DateTime(2000, 1, 1);
                endDateTime = new DateTime(3000, 1, 1);
            }
            else
            {
                startDateTime = new DateTime(month.Year, month.Month, 1);
                endDateTime = startDateTime.AddMonths(1).AddMilliseconds(-5);
            }

            List<Contract.火车票月结对账单> list = new List<Contract.火车票月结对账单>();

            var list1 = (from ttod in entities.TrainTicketOrderDetail
                         join tto in entities.TrainTicketOrder on ttod.TrainTicketOrderID equals tto.ID
                         where tto.CustomerID == customer.ID &&
                               tto.OrderDate >= startDateTime &&
                               tto.OrderDate <= endDateTime &&
                               tto.Status != "已作废"
                         orderby tto.OrderDate, tto.Ref
                         select new Contract.火车票月结对账单()
                         {
                             单据号 = tto.Ref.Trim(),
                             出票日期 = tto.ProvideTicketDate,
                             出发日期 = ttod.DepartDate,
                             出发地 = ttod.DepartLocation,
                             目的地 = ttod.Destination,
                             金额 = ttod.TotalPrice,
                         }).ToList();

            list.AddRange(list1);

            var list2 = (from ttr in entities.TrainTicketReturn
                         join ttod in entities.TrainTicketOrderDetail on ttr.TrainTicketOrderDetailID equals ttod.ID
                         join tto in entities.TrainTicketOrder on ttod.TrainTicketOrderID equals tto.ID
                         where tto.CustomerID == customer.ID &&
                               ttr.OrderDate >= startDateTime &&
                               ttr.OrderDate <= endDateTime &&
                               tto.Status != "已作废"
                         orderby tto.OrderDate, tto.Ref
                         select new Contract.火车票月结对账单()
                         {
                             单据号 = tto.Ref.Trim(),
                             出票日期 = tto.ProvideTicketDate,
                             出发日期 = ttod.DepartDate,
                             出发地 = ttod.DepartLocation,
                             目的地 = ttod.Destination,
                             金额 = ttr.CustomerReturn,
                         }).ToList();

            list.AddRange(list2);

            return (from t in list
                    orderby t.出票日期, t.单据号
                    select t).ToList();
        }

        public List<Contract.酒店月结对账单> GetReport08(Contract.RCustomer customer, DateTime month)
        {
            //if (customer == null)
            //    throw new BusinessException(VerificationText.请输入客户);

            DateTime startDateTime, endDateTime;
            if (month == DateTime.MinValue)
            {
                startDateTime = new DateTime(2000, 1, 1);
                endDateTime = new DateTime(3000, 1, 1);
            }
            else
            {
                startDateTime = new DateTime(month.Year, month.Month, 1);
                endDateTime = startDateTime.AddMonths(1).AddMilliseconds(-5);
            }

            var list = (from hbd in entities.HotelBookingDetail
                        join hb in entities.HotelBooking on hbd.HotelBookingID equals hb.ID
                        where hb.CustomerID == customer.ID &&
                              hb.OrderDate >= startDateTime &&
                              hb.OrderDate <= endDateTime &&
                              hb.Status != "已作废"
                        orderby hb.OrderDate, hb.Ref
                        select new Contract.酒店月结对账单()
                        {
                            单据号 = hb.Ref.Trim(),
                            入住酒店 = hbd.HotelName.Trim(),
                            入住日期 = hbd.CheckInDate,
                            房间数 = hbd.Rooms,
                            积分 = hb.Intergral,
                            晚数 = hbd.Days,
                            金额 = hbd.TotalPrice,
                        }).ToList();

            return list;
        }

        public List<Contract.其他项目月结对账单> GetReport09(Contract.RCustomer customer, DateTime month)
        {
            //if (customer == null)
            //    throw new BusinessException(VerificationText.请输入客户);

            DateTime startDateTime, endDateTime;
            if (month == DateTime.MinValue)
            {
                startDateTime = new DateTime(2000, 1, 1);
                endDateTime = new DateTime(3000, 1, 1);
            }
            else
            {
                startDateTime = new DateTime(month.Year, month.Month, 1);
                endDateTime = startDateTime.AddMonths(1).AddMilliseconds(-5);
            }

            var list = (from ob in entities.OtherBusiness
                        where ob.CustomerID == customer.ID &&
                              ob.OrderDate >= startDateTime &&
                              ob.OrderDate <= endDateTime &&
                              ob.Status != "已作废"
                        orderby ob.OrderDate, ob.Ref
                        select new Contract.其他项目月结对账单()
                        {
                            单据号 = ob.Ref.Trim(),
                            项目 = (from p in entities.Project
                                  where p.ID == ob.ProjectID
                                  select p.Name.Trim()).FirstOrDefault(),
                            项目内容 = ob.Contenct.Trim(),
                            积分 = ob.Intergral,
                            数量 = ob.Qty,
                            项目日期 = ob.ProjectDate,
                            金额 = ob.TotalPrice,
                        }).ToList();

            return list;
        }

        public List<Contract.每日订单报表> GetReport10(DateTime day)
        {
            DateTime startDateTime = new DateTime(day.Year, day.Month, day.Day);
            DateTime endDateTime = startDateTime.AddDays(1).AddMilliseconds(-5);

            List<Contract.每日订单报表> resultList = new List<Contract.每日订单报表>();

            var list1 = (from fod in entities.FlightOrderDetail
                         join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                         where fo.OrderDate >= startDateTime &&
                               fo.OrderDate <= endDateTime &&
                               fo.Status.Trim() != "已作废"
                         select new Contract.每日订单报表()
                         {
                             单据号 = fo.Ref.Trim(),
                             单据类型 = "飞机票",
                             客户 = (from c in entities.Customer
                                   where c.ID == fo.CustomerID
                                   select c.Name).FirstOrDefault(),
                             供应商 = (from v in entities.Vendor
                                    where v.ID == fod.VendorID
                                    select v.Name).FirstOrDefault(),
                             积分 = fod.Intergral,
                             价格 = fod.AccountReceivable,
                             开单日期 = fo.OrderDate,
                             利润 = fo.TotalProfit
                         }).ToList();
            resultList.AddRange(list1);

            var list2 = (from tto in entities.TrainTicketOrder
                         where tto.OrderDate >= startDateTime &&
                               tto.OrderDate <= endDateTime &&
                               tto.Status.Trim() != "已作废"
                         select new Contract.每日订单报表()
                         {
                             单据号 = tto.Ref.Trim(),
                             单据类型 = "火车票",
                             客户 = (from c in entities.Customer
                                   where c.ID == tto.CustomerID
                                   select c.Name).FirstOrDefault(),
                             供应商 = (from v in entities.Vendor
                                    where v.ID == tto.VendorID
                                    select v.Name).FirstOrDefault(),
                             积分 = 0,
                             价格 = tto.TotalPrice,
                             开单日期 = tto.OrderDate,
                             利润 = tto.TotalProfit
                         }).ToList();
            resultList.AddRange(list2);

            var list3 = (from hb in entities.HotelBooking
                         where hb.OrderDate >= startDateTime &&
                               hb.OrderDate <= endDateTime &&
                               hb.Status.Trim() != "已作废"
                         select new Contract.每日订单报表()
                         {
                             单据号 = hb.Ref.Trim(),
                             单据类型 = "酒店",
                             客户 = (from c in entities.Customer
                                   where c.ID == hb.CustomerID
                                   select c.Name).FirstOrDefault(),
                             供应商 = (from v in entities.Vendor
                                    where v.ID == hb.VendorID
                                    select v.Name).FirstOrDefault(),
                             积分 = hb.Intergral,
                             价格 = hb.TotalPrice,
                             开单日期 = hb.OrderDate,
                             利润 = hb.TotalProfit
                         }).ToList();
            resultList.AddRange(list3);

            var list4 = (from ob in entities.OtherBusiness
                         where ob.OrderDate >= startDateTime &&
                               ob.OrderDate <= endDateTime &&
                               ob.Status.Trim() != "已作废"
                         select new Contract.每日订单报表()
                         {
                             单据号 = ob.Ref.Trim(),
                             单据类型 = "其他",
                             客户 = (from c in entities.Customer
                                   where c.ID == ob.CustomerID
                                   select c.Name).FirstOrDefault(),
                             供应商 = (from v in entities.Vendor
                                    where v.ID == ob.VendorID
                                    select v.Name).FirstOrDefault(),
                             积分 = ob.Intergral,
                             价格 = ob.TotalPrice,
                             开单日期 = ob.OrderDate,
                             利润 = ob.TotalProfit
                         }).ToList();
            resultList.AddRange(list4);

            var list5 = (from ob in entities.TouringParty
                         where ob.OrderDate >= startDateTime &&
                               ob.OrderDate <= endDateTime &&
                               ob.Status.Trim() != "已作废"
                         select new Contract.每日订单报表()
                         {
                             单据号 = ob.Ref.Trim(),
                             单据类型 = "其他",
                             客户 = (from c in entities.Customer
                                   where c.ID == ob.CustomerID
                                   select c.Name).FirstOrDefault(),
                             价格 = ob.TotalPrice,
                             开单日期 = ob.OrderDate,
                             利润 = ob.TotalPrice - ob.TotalVendorPrice,
                         }).ToList();
            resultList.AddRange(list5);

            var list6 = (from fc in entities.FlightChange
                         join fod in entities.FlightOrderDetail on fc.FlightOrderDetailID equals fod.ID
                         join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                         where fc.OrderDate >= startDateTime &&
                               fc.OrderDate <= endDateTime &&
                               fo.Status.Trim() != "已作废"
                         select new Contract.每日订单报表()
                         {
                             单据号 = fc.Ref.Trim(),
                             单据类型 = "飞机票改票",
                             客户 = (from c in entities.Customer
                                   where c.ID == fo.CustomerID
                                   select c.Name).FirstOrDefault(),
                             供应商 = (from v in entities.Vendor
                                    where v.ID == fod.VendorID
                                    select v.Name).FirstOrDefault(),
                             积分 = 0,
                             价格 = fc.Price,
                             开单日期 = fc.OrderDate,
                             利润 = fc.Profit
                         }).ToList();
            resultList.AddRange(list6);

            var list7 = (from fr in entities.FlightReturn
                         join fod in entities.FlightOrderDetail on fr.FlightOrderDetailID equals fod.ID
                         join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                         where fr.OrderDate >= startDateTime &&
                               fr.OrderDate <= endDateTime &&
                               fo.Status.Trim() != "已作废"
                         select new Contract.每日订单报表()
                         {
                             单据号 = fr.Ref.Trim(),
                             单据类型 = "飞机票退票",
                             客户 = (from c in entities.Customer
                                   where c.ID == fo.CustomerID
                                   select c.Name).FirstOrDefault(),
                             供应商 = (from v in entities.Vendor
                                    where v.ID == fod.VendorID
                                    select v.Name).FirstOrDefault(),
                             积分 = 0,
                             价格 = fr.CustomerReturnAmount,
                             开单日期 = fr.OrderDate,
                             利润 = fr.Profit
                         }).ToList();
            resultList.AddRange(list7);

            return (from m in resultList
                    orderby m.开单日期, m.单据号
                    select m).ToList();
        }

        public List<Contract.应付款报表> GetReport11()
        {
            List<Contract.应付款报表> resultList = new List<Contract.应付款报表>();
            //var list1 = (from fo in entities.FlightOrder
            //             where fo.VendorStatus != "已付款" &&
            //                   fo.Status != "已作废"
            //             select new Contract.应付款报表()
            //             {
            //                 单据号 = fo.Ref.Trim(),
            //                 单据类型 = "飞机票",
            //                 开单日期 = fo.OrderDate,
            //                 供应商 = (from v in entities.Vendor
            //                        where v.ID == fo.VendorID
            //                        select v.Name.Trim()).FirstOrDefault(),
            //                 金额 = fo.TotalAccountPayable,
            //                 货币 = (from c in entities.Currency
            //                       where c.ID == fo.VendorCurrencyID
            //                       select c.Code.Trim()).FirstOrDefault(),
            //                 人民币金额 = fo.TotalAccountPayable * fo.ExchageRate
            //             }).ToList();
            //resultList.AddRange(list1);

            var list2 = (from tto in entities.TrainTicketOrder
                         where tto.VendorStatus != "已付款" &&
                               tto.Status != "已作废"
                         select new Contract.应付款报表()
                         {
                             单据号 = tto.Ref.Trim(),
                             单据类型 = "火车票",
                             开单日期 = tto.OrderDate,
                             供应商 = (from v in entities.Vendor
                                    where v.ID == tto.VendorID
                                    select v.Name.Trim()).FirstOrDefault(),
                             金额 = tto.TotalVendorPrice,
                             货币 = (from c in entities.Currency
                                   where c.ID == tto.VendorCurrencyID
                                   select c.Code.Trim()).FirstOrDefault(),
                             人民币金额 = tto.TotalVendorPrice * tto.ExchangeRate
                         }).ToList();
            resultList.AddRange(list2);

            var list3 = (from hb in entities.HotelBooking
                         where hb.VendorStatus != "已付款" &&
                               hb.Status != "已作废"
                         select new Contract.应付款报表()
                         {
                             单据号 = hb.Ref.Trim(),
                             单据类型 = "酒店",
                             开单日期 = hb.OrderDate,
                             供应商 = (from v in entities.Vendor
                                    where v.ID == hb.VendorID
                                    select v.Name.Trim()).FirstOrDefault(),
                             金额 = hb.TotalVendorPrice,
                             货币 = (from c in entities.Currency
                                   where c.ID == hb.VendorCurrencyID
                                   select c.Code.Trim()).FirstOrDefault(),
                             人民币金额 = hb.TotalVendorPrice * hb.ExchangeRate
                         }).ToList();
            resultList.AddRange(list3);

            var list4 = (from ob in entities.OtherBusiness
                         where ob.VendorStatus != "已付款" &&
                               ob.Status != "已作废"
                         select new Contract.应付款报表()
                         {
                             单据号 = ob.Ref.Trim(),
                             单据类型 = "其他",
                             开单日期 = ob.OrderDate,
                             供应商 = (from v in entities.Vendor
                                    where v.ID == ob.VendorID
                                    select v.Name.Trim()).FirstOrDefault(),
                             金额 = ob.TotalVendorPrice,
                             货币 = (from c in entities.Currency
                                   where c.ID == ob.CurrencyID
                                   select c.Code.Trim()).FirstOrDefault(),
                             人民币金额 = ob.TotalVendorPrice
                         }).ToList();
            resultList.AddRange(list4);

            var list5 = (from fc in entities.FlightChange
                         where fc.VendorStatus != "已付款"
                         select new Contract.应付款报表()
                         {
                             单据号 = fc.Ref.Trim(),
                             单据类型 = "飞机票改票",
                             开单日期 = fc.OrderDate,
                             供应商 = (from v in entities.Vendor
                                    where v.ID == fc.VendorID
                                    select v.Name.Trim()).FirstOrDefault(),
                             金额 = fc.VendorPrice,
                             货币 = (from c in entities.Currency
                                   where c.ID == fc.VendorCurrencyID
                                   select c.Code.Trim()).FirstOrDefault(),
                             人民币金额 = fc.VendorPrice * fc.ExchangeRate
                         }).ToList();
            resultList.AddRange(list5);

            var list6 = (from fr in entities.FlightReturn
                         where fr.VendorStatus != "已付款"
                         select new Contract.应付款报表()
                         {
                             单据号 = fr.Ref.Trim(),
                             单据类型 = "飞机票退票",
                             开单日期 = fr.OrderDate,
                             供应商 = (from v in entities.Vendor
                                    where v.ID == fr.VendorID
                                    select v.Name.Trim()).FirstOrDefault(),
                             金额 = fr.VendorReturnAmount,
                             货币 = (from c in entities.Currency
                                   where c.ID == fr.VendorCurrencyID
                                   select c.Code.Trim()).FirstOrDefault(),
                             人民币金额 = fr.VendorReturnAmount * fr.ExchangeRate
                         }).ToList();
            resultList.AddRange(list6);

            var list7 = (from ttr in entities.TrainTicketReturn
                         where ttr.VendorStatus != "已付款"
                         select new Contract.应付款报表()
                         {
                             单据号 = ttr.Ref.Trim(),
                             单据类型 = "火车票退票",
                             开单日期 = ttr.OrderDate,
                             供应商 = (from v in entities.Vendor
                                    where v.ID == ttr.VendorID
                                    select v.Name.Trim()).FirstOrDefault(),
                             金额 = ttr.VendorReturn,
                             货币 = (from c in entities.Currency
                                   where c.ID == ttr.VendorCurrencyID
                                   select c.Code.Trim()).FirstOrDefault(),
                             人民币金额 = ttr.VendorReturn * ttr.ExchangeRate
                         }).ToList();
            resultList.AddRange(list7);

            var list8 = (from ip in entities.InsurancePolicy
                         where ip.VendorStatus != "已付款" &&
                               ip.Status == "已用"
                         select new Contract.应付款报表()
                         {
                             单据号 = ip.Ref.Trim(),
                             单据类型 = "保险单",
                             开单日期 = ip.OrderDate.Value,
                             供应商 = (from v in entities.Vendor
                                    where v.ID == ip.VendorID
                                    select v.Name.Trim()).FirstOrDefault(),
                             金额 = ip.BasePrice,
                             货币 = (from c in entities.Currency
                                   where c.ID == ip.CurrencyID
                                   select c.Code.Trim()).FirstOrDefault(),
                             人民币金额 = ip.BasePrice
                         }).ToList();
            resultList.AddRange(list8);

            var list9 = (from ob in entities.TouringParty
                         where ob.VendorStatus != "已付款" &&
                               ob.Status != "已作废"
                         select new Contract.应付款报表()
                         {
                             单据号 = ob.Ref.Trim(),
                             单据类型 = "其他",
                             开单日期 = ob.OrderDate,
                             金额 = ob.TotalVendorPrice,
                             货币 = "CNY",
                             人民币金额 = ob.TotalVendorPrice
                         }).ToList();
            resultList.AddRange(list9);

            return (from p in resultList
                    orderby p.开单日期, p.单据号
                    select p).ToList();
        }

        public List<Contract.应收款报表> GetReport12()
        {
            List<Contract.应收款报表> resultList = new List<Contract.应收款报表>();
            var list1 = (from fo in entities.FlightOrder
                         where fo.CustomerStatus != "已付款" &&
                               fo.Status != "已作废"
                         select new Contract.应收款报表()
                         {
                             单据号 = fo.Ref.Trim(),
                             单据类型 = "飞机票",
                             开单日期 = fo.OrderDate,
                             客户 = (from v in entities.Customer
                                   where v.ID == fo.CustomerID
                                   select v.Name.Trim()).FirstOrDefault(),
                             金额 = fo.TotalAccountReceivable,
                             结算方式 = fo.CreditTerm.Trim(),
                         }).ToList();
            resultList.AddRange(list1);

            var list2 = (from tto in entities.TrainTicketOrder
                         where tto.CustomerStatus != "已付款" &&
                               tto.Status != "已作废"
                         select new Contract.应收款报表()
                         {
                             单据号 = tto.Ref.Trim(),
                             单据类型 = "火车票",
                             开单日期 = tto.OrderDate,
                             客户 = (from v in entities.Customer
                                   where v.ID == tto.CustomerID
                                   select v.Name.Trim()).FirstOrDefault(),
                             金额 = tto.TotalPrice,
                             结算方式 = tto.CreditTerm.Trim(),
                         }).ToList();
            resultList.AddRange(list2);

            var list3 = (from hb in entities.HotelBooking
                         where hb.CustomerStatus != "已付款" &&
                               hb.Status != "已作废" &&
                               hb.PaymentType == "预付"
                         select new Contract.应收款报表()
                         {
                             单据号 = hb.Ref.Trim(),
                             单据类型 = "酒店",
                             开单日期 = hb.OrderDate,
                             客户 = (from v in entities.Customer
                                   where v.ID == hb.CustomerID
                                   select v.Name.Trim()).FirstOrDefault(),
                             金额 = hb.TotalPrice,
                             结算方式 = "现付",
                         }).ToList();
            resultList.AddRange(list3);

            var list4 = (from ob in entities.OtherBusiness
                         where ob.CustomerStatus != "已付款" &&
                               ob.Status != "已作废"
                         select new Contract.应收款报表()
                         {
                             单据号 = ob.Ref.Trim(),
                             单据类型 = "其他",
                             开单日期 = ob.OrderDate,
                             客户 = (from v in entities.Customer
                                   where v.ID == ob.CustomerID
                                   select v.Name.Trim()).FirstOrDefault(),
                             金额 = ob.TotalPrice,
                             结算方式 = ob.CreditTerm.Trim(),
                         }).ToList();
            resultList.AddRange(list4);

            var list5 = (from fc in entities.FlightChange
                         where fc.CustomerStatus != "已付款"
                         select new Contract.应收款报表()
                         {
                             单据号 = fc.Ref.Trim(),
                             单据类型 = "飞机票改票",
                             开单日期 = fc.OrderDate,
                             客户 = (from v in entities.Customer
                                   where v.ID == fc.CustomerID
                                   select v.Name.Trim()).FirstOrDefault(),
                             金额 = fc.Price,
                             结算方式 = (from fo in entities.FlightOrder
                                     where fo.ID == (from fod in entities.FlightOrderDetail
                                                     where fod.ID == fc.FlightOrderDetailID
                                                     select fod.FlightOrderID).FirstOrDefault()
                                     select fo.CreditTerm.Trim()).FirstOrDefault()
                         }).ToList();
            resultList.AddRange(list5);

            var list6 = (from fr in entities.FlightReturn
                         where fr.CustomerStatus != "已付款"
                         select new Contract.应收款报表()
                         {
                             单据号 = fr.Ref.Trim(),
                             单据类型 = "飞机票退票",
                             开单日期 = fr.OrderDate,
                             客户 = (from v in entities.Customer
                                   where v.ID == fr.CustomerID
                                   select v.Name.Trim()).FirstOrDefault(),
                             金额 = fr.CustomerReturnAmount,
                             结算方式 = (from fo in entities.FlightOrder
                                     where fo.ID == (from fod in entities.FlightOrderDetail
                                                     where fod.ID == fr.FlightOrderDetailID
                                                     select fod.FlightOrderID).FirstOrDefault()
                                     select fo.CreditTerm.Trim()).FirstOrDefault()
                         }).ToList();
            resultList.AddRange(list6);

            var list7 = (from ttr in entities.TrainTicketReturn
                         where ttr.CustomerStatus != "已付款"
                         select new Contract.应收款报表()
                         {
                             单据号 = ttr.Ref.Trim(),
                             单据类型 = "火车票退票",
                             开单日期 = ttr.OrderDate,
                             客户 = (from v in entities.Customer
                                   where v.ID == ttr.CustomerID
                                   select v.Name.Trim()).FirstOrDefault(),
                             金额 = ttr.CustomerReturn,
                             结算方式 = (from tto in entities.TrainTicketOrder
                                     where tto.ID == (from ttod in entities.TrainTicketOrderDetail
                                                      where ttod.ID == ttr.TrainTicketOrderDetailID
                                                      select ttod.TrainTicketOrderID).FirstOrDefault()
                                     select tto.CreditTerm.Trim()).FirstOrDefault()
                         }).ToList();
            resultList.AddRange(list7);

            var list9 = (from ob in entities.TouringParty
                         where ob.CustomerStatus != "已付款" &&
                               ob.Status.Trim() == "完成"
                         select new Contract.应收款报表()
                         {
                             单据号 = ob.Ref.Trim(),
                             单据类型 = "自组团",
                             开单日期 = ob.OrderDate,
                             客户 = (from v in entities.Customer
                                   where v.ID == ob.CustomerID
                                   select v.Name.Trim()).FirstOrDefault(),
                             金额 = ob.TotalPrice,
                         }).ToList();
            resultList.AddRange(list9);

            return (from p in resultList
                    orderby p.开单日期, p.单据号
                    select p).ToList();
        }

        public List<Contract.已收款报表> GetReport13(Contract.RCustomer customer, DateTime startDay, DateTime endDay)
        {
            //if (customer == null)
            //    throw new BusinessException(VerificationText.请输入客户);

            DateTime startDateTime = new DateTime(startDay.Year, startDay.Month, startDay.Day);
            DateTime endDateTime = (new DateTime(endDay.Year, endDay.Month, endDay.Day)).AddDays(1).AddMilliseconds(-5);


            if (customer == null)
            {
                return (from cs in entities.CustomerSettlement
                        where cs.SettlementDate >= startDateTime &&
                              cs.SettlementDate <= endDateTime
                        orderby cs.SettlementDate, cs.Ref
                        select new Contract.已收款报表()
                        {
                            单据号 = cs.Ref.Trim(),
                            单据类型 = cs.Type.Trim(),
                            日期 = cs.SettlementDate,
                            金额 = cs.Amount,
                            使用积分 = cs.Intergral,
                            支付方式 = cs.PayType.Trim(),
                            备注 = cs.Remark.Trim(),
                            客户 = (from c in entities.Customer
                                  where c.ID == cs.CustomerID
                                  select c.Name.Trim()).FirstOrDefault()
                        }).ToList();
            }
            else
            {
                return (from cs in entities.CustomerSettlement
                        where cs.CustomerID == customer.ID &&
                              cs.SettlementDate >= startDateTime &&
                              cs.SettlementDate <= endDateTime
                        orderby cs.SettlementDate, cs.Ref
                        select new Contract.已收款报表()
                        {
                            单据号 = cs.Ref.Trim(),
                            单据类型 = cs.Type.Trim(),
                            日期 = cs.SettlementDate,
                            金额 = cs.Amount,
                            使用积分 = cs.Intergral,
                            支付方式 = cs.PayType.Trim(),
                            备注 = cs.Remark.Trim(),
                            客户 = customer.Name
                        }).ToList();
            }
        }

        public List<Contract.已付款报表> GetReport14(Contract.RVendor vendor, DateTime startDay, DateTime endDay)
        {
            //if (vendor == null)
            //    throw new BusinessException(VerificationText.请输入供应商);

            DateTime startDateTime = new DateTime(startDay.Year, startDay.Month, startDay.Day);
            DateTime endDateTime = (new DateTime(endDay.Year, endDay.Month, endDay.Day)).AddDays(1).AddMilliseconds(-5);

            List<Contract.已付款报表> list = null;
            if (vendor != null)
                list = (from vs in entities.VendorSettlement
                        where vs.VendorID == vendor.ID &&
                              vs.PostingDate >= startDateTime &&
                              vs.PostingDate <= endDateTime &&
                              ((vs.PayType.Trim() != "现金" && vs.BankAccountID != null) ||
                                (vs.PayType.Trim() == "现金" && vs.BankAccountID == null))
                        orderby vs.PostingDate, vs.Ref
                        select new Contract.已付款报表()
                        {
                            单据号 = vs.Ref.Trim(),
                            单据类型 = vs.Type.Trim(),
                            过账日期 = vs.PostingDate,
                            金额 = vs.Amount,
                            货币 = (from c in entities.Currency
                                  where c.ID == vs.CurrencyID
                                  select c.Code.Trim()).FirstOrDefault(),
                            人民币金额 = vs.Amount * vs.ExchangeRate,
                            支付方式 = vs.PayType.Trim(),
                            备注 = vs.Remark.Trim(),
                            供应商 = vs.Vendor.Name.Trim(),
                        }).ToList();
            else
                list = (from vs in entities.VendorSettlement
                        where vs.PostingDate >= startDateTime &&
                              vs.PostingDate <= endDateTime &&
                              ((vs.PayType.Trim() != "现金" && vs.BankAccountID != null) ||
                                (vs.PayType.Trim() == "现金" && vs.BankAccountID == null))
                        orderby vs.PostingDate, vs.Ref
                        select new Contract.已付款报表()
                        {
                            单据号 = vs.Ref.Trim(),
                            单据类型 = vs.Type.Trim(),
                            过账日期 = vs.PostingDate,
                            金额 = vs.Amount,
                            货币 = (from c in entities.Currency
                                  where c.ID == vs.CurrencyID
                                  select c.Code.Trim()).FirstOrDefault(),
                            人民币金额 = vs.Amount * vs.ExchangeRate,
                            支付方式 = vs.PayType.Trim(),
                            备注 = vs.Remark.Trim(),
                            供应商 = vs.Vendor.Name.Trim(),
                        }).ToList();


            return list;
        }

        public Contract.资金流动报表 GetReport15()
        {
            Contract.资金流动报表 资金流动报表 = new Contract.资金流动报表();
            资金流动报表.日期 = DateTime.Now;

            DateTime startDateTime = new DateTime(资金流动报表.日期.Year, 资金流动报表.日期.Month, 资金流动报表.日期.Day);
            DateTime endDateTime = startDateTime.AddDays(1).AddMilliseconds(-5);

            var templist = (from vs in entities.VendorSettlement
                            where vs.PostingDate >= startDateTime &&
                                  vs.PostingDate <= endDateTime
                            select vs.Amount);
            if (templist.Count() != 0)
                资金流动报表.今日总付帐 = templist.Sum();

            var 应收帐清单 = GetReport12();

            foreach (var 应收帐 in 应收帐清单)
            {
                if (应收帐.结算方式 == "月结")
                {
                    资金流动报表.月结应收总帐 += 应收帐.金额;
                    if (应收帐.开单日期.Year == 资金流动报表.日期.Year &&
                        应收帐.开单日期.Month == 资金流动报表.日期.Month &&
                        应收帐.开单日期.Day == 资金流动报表.日期.Day)
                    {
                        资金流动报表.今日月结应收总帐 += 应收帐.金额;
                    }
                }
                if (应收帐.结算方式 == "现结")
                {
                    资金流动报表.散客应收总帐 += 应收帐.金额;
                    if (应收帐.开单日期.Year == 资金流动报表.日期.Year &&
                        应收帐.开单日期.Month == 资金流动报表.日期.Month &&
                        应收帐.开单日期.Day == 资金流动报表.日期.Day)
                    {
                        资金流动报表.今日散客应收总帐 += 应收帐.金额;
                    }
                }
            }

            资金流动报表.预存总款 = (from c in entities.Customer select c.Amount).Sum().Value;

            List<Contract.Currency> currencyList = new BaseBL().GetAllCurrency();
            List<Contract.BankAccount> bankAccountList = new MasterBL().GetAllBankAccount();
            foreach (Contract.BankAccount bankAccount in bankAccountList)
            {
                Contract.资金流动报表明细 资金流动报表明细 = new Contract.资金流动报表明细();
                资金流动报表明细.银行账户 = bankAccount.Name;
                资金流动报表明细.余额 = string.Format("{0} {1}", bankAccount.Currency.Code, bankAccount.Amount);
                if (bankAccount.Currency.Code != "CNY")
                {
                    Contract.Currency currency = currencyList.Find(c => c.Code == bankAccount.Currency.Code);
                    资金流动报表明细.人民币余额 = bankAccount.Amount * currency.ExchangeRate.Value;
                }
                else
                {
                    资金流动报表明细.人民币余额 = bankAccount.Amount;
                }

                var vsList = from vs in entities.VendorSettlement
                             where vs.PostingDate >= startDateTime &&
                                   vs.PostingDate <= endDateTime &&
                                   vs.BankAccountID == bankAccount.ID
                             select vs.Amount;

                if (vsList.Count() == 0)
                    资金流动报表明细.出 = 0;
                else
                    资金流动报表明细.出 = vsList.Sum();

                var tranOutList = (from t in entities.AmountTransfer
                                   where t.TransferDate >= startDateTime &&
                                         t.TransferDate <= endDateTime &&
                                         t.SourceID == bankAccount.ID
                                   select t.Amount + t.ServiceCharge);
                if (tranOutList.Count() != 0)
                    资金流动报表明细.出 += tranOutList.Sum();

                var csList = from cs in entities.CustomerSettlement
                             where cs.SettlementDate >= startDateTime &&
                                   cs.SettlementDate <= endDateTime &&
                                   cs.BankAccountID == bankAccount.ID
                             select cs.Amount;

                if (csList.Count() == 0)
                    资金流动报表明细.入账总额 = 0;
                else
                    资金流动报表明细.入账总额 = csList.Sum();

                var tranInList = (from t in entities.AmountTransfer
                                  where t.ReceiveDate >= startDateTime &&
                                        t.ReceiveDate <= endDateTime &&
                                        t.TargetID == bankAccount.ID
                                  select t.Amount * t.ExchangeRate);
                if (tranInList.Count() != 0)
                    资金流动报表明细.入账总额 += tranInList.Sum();

                资金流动报表.总现金 += 资金流动报表明细.人民币余额;
                资金流动报表.总资金 += 资金流动报表明细.人民币余额;

                资金流动报表.明细.Add(资金流动报表明细);
            }

            var vsCashList = from vs in entities.VendorSettlement
                             where vs.PostingDate >= startDateTime &&
                                   vs.PostingDate <= endDateTime &&
                                   vs.PayType == "现金"
                             select vs.Amount;

            if (vsCashList.Count() == 0)
                资金流动报表.今日现金支出 = 0;
            else
                资金流动报表.今日现金支出 = vsCashList.Sum();

            var csCashList = from vs in entities.CustomerSettlement
                             where vs.SettlementDate >= startDateTime &&
                                   vs.SettlementDate <= endDateTime &&
                                   vs.PayType == "现金"
                             select vs.Amount;

            if (csCashList.Count() == 0)
                资金流动报表.今日现金收入 = 0;
            else
                资金流动报表.今日现金收入 = csCashList.Sum();

            资金流动报表.总资金 += 资金流动报表.散客应收总帐;
            资金流动报表.总资金 += 资金流动报表.月结应收总帐;
            //资金流动报表.总资金 -= 资金流动报表.应付总帐;

            //var csList1 = from vs in entities.CustomerSettlement
            //             where vs.SettlementDate >= startDateTime &&
            //                   vs.SettlementDate <= endDateTime 
            //             select vs.Amount;

            //var opList = from vs in entities.OtherPayment
            //             where vs.Date >= startDateTime &&
            //                   vs.Date <= endDateTime
            //             select vs.Amount;

            //decimal 今日收款 = 0;
            //if (csList1.Count() != 0)
            //    今日收款 = csList1.Sum();

            //decimal 今日其他支出 = 0;
            //if (opList.Count() != 0)
            //    今日其他支出 = opList.Sum();

            var 今日利润List = GetReport05(startDateTime, endDateTime);

            if (今日利润List.Count == 0)
                资金流动报表.今日利润 = 0;
            else
                资金流动报表.今日利润 = 今日利润List.Sum(a => a.利润);
            资金流动报表.预期利润 = 资金流动报表.散客应收总帐 + 资金流动报表.月结应收总帐 - 资金流动报表.应付总帐;

            return 资金流动报表;
        }

        public List<Contract.每日订单报表> GetReport16(Contract.User salesMan, DateTime month)
        {
            //if (salesMan == null)
            //    throw new BusinessException(VerificationText.请输入业务员);

            DateTime startDateTime = new DateTime(month.Year, month.Month, 1);
            DateTime endDateTime = startDateTime.AddMonths(1).AddMilliseconds(-5);

            List<Contract.每日订单报表> resultList = new List<Contract.每日订单报表>();

            if (salesMan != null)
            {
                var list1 = (from fod in entities.FlightOrderDetail
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fo.SalesmanID == salesMan.ID &&
                                   fo.OrderDate >= startDateTime &&
                                   fo.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             orderby fo.OrderDate, fod.Seq
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fo.Ref.Trim(),
                                 单据类型 = "飞机票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = fod.Intergral,
                                 价格 = fod.AccountReceivable,
                                 开单日期 = fo.OrderDate,
                                 利润 = fod.Profit
                             }).ToList();
                resultList.AddRange(list1);
            }
            else
            {
                var list1 = (from fod in entities.FlightOrderDetail
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fo.OrderDate >= startDateTime &&
                                   fo.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             orderby fo.OrderDate, fod.Seq
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fo.Ref.Trim(),
                                 单据类型 = "飞机票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = fod.Intergral,
                                 价格 = fod.AccountReceivable,
                                 开单日期 = fo.OrderDate,
                                 利润 = fod.Profit
                             }).ToList();
                resultList.AddRange(list1);
            }

            if (salesMan != null)
            {
                var list6 = (from fc in entities.FlightChange
                             join fod in entities.FlightOrderDetail on fc.FlightOrderDetailID equals fod.ID
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fo.SalesmanID == salesMan.ID &&
                                   fc.OrderDate >= startDateTime &&
                                   fc.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fc.Ref.Trim(),
                                 单据类型 = "飞机票改票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = fc.Price,
                                 开单日期 = fc.OrderDate,
                                 利润 = fc.Profit
                             }).ToList();
                resultList.AddRange(list6);
            }
            else
            {
                var list6 = (from fc in entities.FlightChange
                             join fod in entities.FlightOrderDetail on fc.FlightOrderDetailID equals fod.ID
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fc.OrderDate >= startDateTime &&
                                   fc.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fc.Ref.Trim(),
                                 单据类型 = "飞机票改票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = fc.Price,
                                 开单日期 = fc.OrderDate,
                                 利润 = fc.Profit
                             }).ToList();
                resultList.AddRange(list6);
            }

            if (salesMan != null)
            {
                var list7 = (from fr in entities.FlightReturn
                             join fod in entities.FlightOrderDetail on fr.FlightOrderDetailID equals fod.ID
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fo.SalesmanID == salesMan.ID &&
                                   fr.OrderDate >= startDateTime &&
                                   fr.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fr.Ref.Trim(),
                                 单据类型 = "飞机票退票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = fr.CustomerReturnAmount,
                                 开单日期 = fr.OrderDate,
                                 利润 = fr.Profit
                             }).ToList();
                resultList.AddRange(list7);
            }
            else
            {
                var list7 = (from fr in entities.FlightReturn
                             join fod in entities.FlightOrderDetail on fr.FlightOrderDetailID equals fod.ID
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fr.OrderDate >= startDateTime &&
                                   fr.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fr.Ref.Trim(),
                                 单据类型 = "飞机票退票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = fr.CustomerReturnAmount,
                                 开单日期 = fr.OrderDate,
                                 利润 = fr.Profit
                             }).ToList();
                resultList.AddRange(list7);
            }

            if (salesMan != null)
            {
                var list2 = (from tto in entities.TrainTicketOrder
                             where tto.SalesmanID == salesMan.ID &&
                                   tto.OrderDate >= startDateTime &&
                                   tto.OrderDate <= endDateTime &&
                                   tto.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = tto.Ref.Trim(),
                                 单据类型 = "火车票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == tto.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == tto.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = tto.TotalPrice,
                                 开单日期 = tto.OrderDate,
                                 利润 = tto.TotalProfit
                             }).ToList();
                resultList.AddRange(list2);
            }
            else
            {
                var list2 = (from tto in entities.TrainTicketOrder
                             where tto.OrderDate >= startDateTime &&
                                   tto.OrderDate <= endDateTime &&
                                   tto.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = tto.Ref.Trim(),
                                 单据类型 = "火车票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == tto.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == tto.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = tto.TotalPrice,
                                 开单日期 = tto.OrderDate,
                                 利润 = tto.TotalProfit
                             }).ToList();
                resultList.AddRange(list2);
            }

            if (salesMan != null)
            {
                var list8 = (from ttr in entities.TrainTicketReturn
                             join ttod in entities.TrainTicketOrderDetail on ttr.TrainTicketOrderDetailID equals ttod.ID
                             join tto in entities.TrainTicketOrder on ttod.TrainTicketOrderID equals tto.ID
                             where tto.SalesmanID == salesMan.ID &&
                                   ttr.OrderDate >= startDateTime &&
                                   ttr.OrderDate <= endDateTime &&
                                   tto.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ttr.Ref.Trim(),
                                 单据类型 = "火车票退票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == tto.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == tto.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = ttr.CustomerReturn,
                                 开单日期 = ttr.OrderDate,
                                 利润 = ttr.Profit
                             }).ToList();
                resultList.AddRange(list8);
            }
            else
            {
                var list8 = (from ttr in entities.TrainTicketReturn
                             join ttod in entities.TrainTicketOrderDetail on ttr.TrainTicketOrderDetailID equals ttod.ID
                             join tto in entities.TrainTicketOrder on ttod.TrainTicketOrderID equals tto.ID
                             where ttr.OrderDate >= startDateTime &&
                                   ttr.OrderDate <= endDateTime &&
                                   tto.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ttr.Ref.Trim(),
                                 单据类型 = "火车票退票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == tto.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == tto.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = ttr.CustomerReturn,
                                 开单日期 = ttr.OrderDate,
                                 利润 = ttr.Profit
                             }).ToList();
                resultList.AddRange(list8);
            }

            if (salesMan != null)
            {
                var list3 = (from hb in entities.HotelBooking
                             where hb.SalesmanID == salesMan.ID &&
                                   hb.OrderDate >= startDateTime &&
                                   hb.OrderDate <= endDateTime &&
                                   hb.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = hb.Ref.Trim(),
                                 单据类型 = "酒店",
                                 客户 = (from c in entities.Customer
                                       where c.ID == hb.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == hb.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = hb.Intergral,
                                 价格 = hb.TotalPrice,
                                 开单日期 = hb.OrderDate,
                                 利润 = hb.TotalProfit
                             }).ToList();
                resultList.AddRange(list3);
            }
            else
            {
                var list3 = (from hb in entities.HotelBooking
                             where hb.OrderDate >= startDateTime &&
                                   hb.OrderDate <= endDateTime &&
                                   hb.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = hb.Ref.Trim(),
                                 单据类型 = "酒店",
                                 客户 = (from c in entities.Customer
                                       where c.ID == hb.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == hb.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = hb.Intergral,
                                 价格 = hb.TotalPrice,
                                 开单日期 = hb.OrderDate,
                                 利润 = hb.TotalProfit
                             }).ToList();
                resultList.AddRange(list3);
            }

            if (salesMan != null)
            {
                var list4 = (from ob in entities.OtherBusiness
                             where ob.SalesmanID == salesMan.ID &&
                                   ob.OrderDate >= startDateTime &&
                                   ob.OrderDate <= endDateTime &&
                                   ob.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ob.Ref.Trim(),
                                 单据类型 = "其他",
                                 客户 = (from c in entities.Customer
                                       where c.ID == ob.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == ob.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = ob.Intergral,
                                 价格 = ob.TotalPrice,
                                 开单日期 = ob.OrderDate,
                                 利润 = ob.TotalProfit
                             }).ToList();
                resultList.AddRange(list4);
            }
            else
            {
                var list4 = (from ob in entities.OtherBusiness
                             where ob.OrderDate >= startDateTime &&
                                   ob.OrderDate <= endDateTime &&
                                   ob.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ob.Ref.Trim(),
                                 单据类型 = "其他",
                                 客户 = (from c in entities.Customer
                                       where c.ID == ob.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == ob.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = ob.Intergral,
                                 价格 = ob.TotalPrice,
                                 开单日期 = ob.OrderDate,
                                 利润 = ob.TotalProfit
                             }).ToList();
                resultList.AddRange(list4);
            }

            if (salesMan != null)
            {
                var list5 = (from ob in entities.TouringParty
                             where ob.OrderUserID == salesMan.ID &&
                                   ob.OrderDate >= startDateTime &&
                                   ob.OrderDate <= endDateTime &&
                                   ob.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ob.Ref.Trim(),
                                 单据类型 = "其他",
                                 客户 = (from c in entities.Customer
                                       where c.ID == ob.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 价格 = ob.TotalPrice,
                                 开单日期 = ob.OrderDate,
                                 利润 = ob.TotalPrice - ob.TotalVendorPrice,
                             }).ToList();
                resultList.AddRange(list5);
            }
            else
            {
                var list5 = (from ob in entities.TouringParty
                             where ob.OrderDate >= startDateTime &&
                                   ob.OrderDate <= endDateTime &&
                                   ob.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ob.Ref.Trim(),
                                 单据类型 = "其他",
                                 客户 = (from c in entities.Customer
                                       where c.ID == ob.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 价格 = ob.TotalPrice,
                                 开单日期 = ob.OrderDate,
                                 利润 = ob.TotalPrice - ob.TotalVendorPrice,
                             }).ToList();
                resultList.AddRange(list5);
            }

            return (from m in resultList
                    orderby m.开单日期, m.单据号
                    select m).ToList();
        }

        public List<Contract.每日订单报表> GetReport17(Contract.User provideUser, DateTime month)
        {
            //if (provideUser == null)
            //    throw new BusinessException(VerificationText.请输入订票人);

            DateTime startDateTime = new DateTime(month.Year, month.Month, 1);
            DateTime endDateTime = startDateTime.AddMonths(1).AddMilliseconds(-5);

            List<Contract.每日订单报表> resultList = new List<Contract.每日订单报表>();

            if (provideUser != null)
            {
                var list1 = (from fod in entities.FlightOrderDetail
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fo.ProvideTicketUserID == provideUser.ID &&
                                   fo.OrderDate >= startDateTime &&
                                   fo.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fo.Ref.Trim(),
                                 单据类型 = "飞机票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = fod.Intergral,
                                 价格 = fod.AccountReceivable,
                                 开单日期 = fo.OrderDate,
                                 利润 = fo.TotalProfit
                             }).ToList();
                resultList.AddRange(list1);
            }
            else
            {
                var list1 = (from fod in entities.FlightOrderDetail
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fo.OrderDate >= startDateTime &&
                                   fo.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fo.Ref.Trim(),
                                 单据类型 = "飞机票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = fod.Intergral,
                                 价格 = fod.AccountReceivable,
                                 开单日期 = fo.OrderDate,
                                 利润 = fo.TotalProfit
                             }).ToList();
                resultList.AddRange(list1);
            }

            if (provideUser != null)
            {
                var list6 = (from fc in entities.FlightChange
                             join fod in entities.FlightOrderDetail on fc.FlightOrderDetailID equals fod.ID
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fo.ProvideTicketUserID == provideUser.ID &&
                                   fc.OrderDate >= startDateTime &&
                                   fc.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fc.Ref.Trim(),
                                 单据类型 = "飞机票改票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = fc.Price,
                                 开单日期 = fc.OrderDate,
                                 利润 = fc.Profit
                             }).ToList();
                resultList.AddRange(list6);
            }
            else
            {
                var list6 = (from fc in entities.FlightChange
                             join fod in entities.FlightOrderDetail on fc.FlightOrderDetailID equals fod.ID
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fc.OrderDate >= startDateTime &&
                                   fc.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fc.Ref.Trim(),
                                 单据类型 = "飞机票改票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = fc.Price,
                                 开单日期 = fc.OrderDate,
                                 利润 = fc.Profit
                             }).ToList();
                resultList.AddRange(list6);
            }

            if (provideUser != null)
            {
                var list7 = (from fr in entities.FlightReturn
                             join fod in entities.FlightOrderDetail on fr.FlightOrderDetailID equals fod.ID
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fo.ProvideTicketUserID == provideUser.ID &&
                                   fr.OrderDate >= startDateTime &&
                                   fr.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fr.Ref.Trim(),
                                 单据类型 = "飞机票退票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = fr.CustomerReturnAmount,
                                 开单日期 = fr.OrderDate,
                                 利润 = fr.Profit
                             }).ToList();
                resultList.AddRange(list7);
            }
            else
            {
                var list7 = (from fr in entities.FlightReturn
                             join fod in entities.FlightOrderDetail on fr.FlightOrderDetailID equals fod.ID
                             join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                             where fr.OrderDate >= startDateTime &&
                                   fr.OrderDate <= endDateTime &&
                                   fo.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = fr.Ref.Trim(),
                                 单据类型 = "飞机票退票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == fo.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == fod.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = fr.CustomerReturnAmount,
                                 开单日期 = fr.OrderDate,
                                 利润 = fr.Profit
                             }).ToList();
                resultList.AddRange(list7);
            }

            if (provideUser != null)
            {
                var list2 = (from tto in entities.TrainTicketOrder
                             where tto.ProvideTicketUserID == provideUser.ID &&
                                   tto.OrderDate >= startDateTime &&
                                   tto.OrderDate <= endDateTime &&
                                   tto.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = tto.Ref.Trim(),
                                 单据类型 = "火车票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == tto.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == tto.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = tto.TotalPrice,
                                 开单日期 = tto.OrderDate,
                                 利润 = tto.TotalProfit
                             }).ToList();
                resultList.AddRange(list2);
            }
            else
            {
                var list2 = (from tto in entities.TrainTicketOrder
                             where tto.OrderDate >= startDateTime &&
                                   tto.OrderDate <= endDateTime &&
                                   tto.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = tto.Ref.Trim(),
                                 单据类型 = "火车票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == tto.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == tto.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = tto.TotalPrice,
                                 开单日期 = tto.OrderDate,
                                 利润 = tto.TotalProfit
                             }).ToList();
                resultList.AddRange(list2);
            }

            if (provideUser != null)
            {
                var list8 = (from ttr in entities.TrainTicketReturn
                             join ttod in entities.TrainTicketOrderDetail on ttr.TrainTicketOrderDetailID equals ttod.ID
                             join tto in entities.TrainTicketOrder on ttod.TrainTicketOrderID equals tto.ID
                             where tto.ProvideTicketUserID == provideUser.ID &&
                                   ttr.OrderDate >= startDateTime &&
                                   ttr.OrderDate <= endDateTime &&
                                   tto.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ttr.Ref.Trim(),
                                 单据类型 = "火车票退票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == tto.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == tto.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = ttr.CustomerReturn,
                                 开单日期 = ttr.OrderDate,
                                 利润 = ttr.Profit
                             }).ToList();
                resultList.AddRange(list8);
            }
            else
            {
                var list8 = (from ttr in entities.TrainTicketReturn
                             join ttod in entities.TrainTicketOrderDetail on ttr.TrainTicketOrderDetailID equals ttod.ID
                             join tto in entities.TrainTicketOrder on ttod.TrainTicketOrderID equals tto.ID
                             where ttr.OrderDate >= startDateTime &&
                                   ttr.OrderDate <= endDateTime &&
                                   tto.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ttr.Ref.Trim(),
                                 单据类型 = "火车票退票",
                                 客户 = (from c in entities.Customer
                                       where c.ID == tto.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == tto.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = 0,
                                 价格 = ttr.CustomerReturn,
                                 开单日期 = ttr.OrderDate,
                                 利润 = ttr.Profit
                             }).ToList();
                resultList.AddRange(list8);
            }

            if (provideUser != null)
            {
                var list3 = (from hb in entities.HotelBooking
                             where hb.ProvideTicketUserID == provideUser.ID &&
                                   hb.OrderDate >= startDateTime &&
                                   hb.OrderDate <= endDateTime &&
                                   hb.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = hb.Ref.Trim(),
                                 单据类型 = "酒店",
                                 客户 = (from c in entities.Customer
                                       where c.ID == hb.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == hb.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = hb.Intergral,
                                 价格 = hb.TotalPrice,
                                 开单日期 = hb.OrderDate,
                                 利润 = hb.TotalProfit
                             }).ToList();
                resultList.AddRange(list3);
            }
            else
            {
                var list3 = (from hb in entities.HotelBooking
                             where hb.OrderDate >= startDateTime &&
                                   hb.OrderDate <= endDateTime &&
                                   hb.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = hb.Ref.Trim(),
                                 单据类型 = "酒店",
                                 客户 = (from c in entities.Customer
                                       where c.ID == hb.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == hb.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = hb.Intergral,
                                 价格 = hb.TotalPrice,
                                 开单日期 = hb.OrderDate,
                                 利润 = hb.TotalProfit
                             }).ToList();
                resultList.AddRange(list3);
            }

            if (provideUser != null)
            {
                var list4 = (from ob in entities.OtherBusiness
                             where ob.ProvideTicketUserID == provideUser.ID &&
                                   ob.OrderDate >= startDateTime &&
                                   ob.OrderDate <= endDateTime &&
                                   ob.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ob.Ref.Trim(),
                                 单据类型 = "其他",
                                 客户 = (from c in entities.Customer
                                       where c.ID == ob.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == ob.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = ob.Intergral,
                                 价格 = ob.TotalPrice,
                                 开单日期 = ob.OrderDate,
                                 利润 = ob.TotalProfit
                             }).ToList();
                resultList.AddRange(list4);
            }
            else
            {
                var list4 = (from ob in entities.OtherBusiness
                             where ob.OrderDate >= startDateTime &&
                                   ob.OrderDate <= endDateTime &&
                                   ob.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ob.Ref.Trim(),
                                 单据类型 = "其他",
                                 客户 = (from c in entities.Customer
                                       where c.ID == ob.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 供应商 = (from v in entities.Vendor
                                        where v.ID == ob.VendorID
                                        select v.Name).FirstOrDefault(),
                                 积分 = ob.Intergral,
                                 价格 = ob.TotalPrice,
                                 开单日期 = ob.OrderDate,
                                 利润 = ob.TotalProfit
                             }).ToList();
                resultList.AddRange(list4);
            }

            if (provideUser != null)
            {
                var list5 = (from ob in entities.TouringParty
                             where ob.ProvideTicketUserID == provideUser.ID &&
                                   ob.OrderDate >= startDateTime &&
                                   ob.OrderDate <= endDateTime &&
                                   ob.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ob.Ref.Trim(),
                                 单据类型 = "其他",
                                 客户 = (from c in entities.Customer
                                       where c.ID == ob.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 价格 = ob.TotalPrice,
                                 开单日期 = ob.OrderDate,
                                 利润 = ob.TotalPrice - ob.TotalVendorPrice,
                             }).ToList();
                resultList.AddRange(list5);
            }
            else
            {
                var list5 = (from ob in entities.TouringParty
                             where ob.OrderDate >= startDateTime &&
                                   ob.OrderDate <= endDateTime &&
                                   ob.Status.Trim() != "已作废"
                             select new Contract.每日订单报表()
                             {
                                 单据号 = ob.Ref.Trim(),
                                 单据类型 = "其他",
                                 客户 = (from c in entities.Customer
                                       where c.ID == ob.CustomerID
                                       select c.Name).FirstOrDefault(),
                                 价格 = ob.TotalPrice,
                                 开单日期 = ob.OrderDate,
                                 利润 = ob.TotalPrice - ob.TotalVendorPrice,
                             }).ToList();
                resultList.AddRange(list5);
            }

            return (from m in resultList
                    orderby m.开单日期, m.单据号
                    select m).ToList();
        }

        public List<Contract.飞机总表> GetReport18(DateTime startDate, DateTime endDate)
        {
            DateTime startDateTime = new DateTime(startDate.Year, startDate.Month, startDate.Day);
            DateTime endDateTime = (new DateTime(endDate.Year, endDate.Month, endDate.Day)).AddDays(1).AddMilliseconds(-3);

            List<Contract.飞机总表> list = new List<Contract.飞机总表>();

            var list1 = (from fod in entities.FlightOrderDetail
                         join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                         where fo.OrderDate >= startDateTime &&
                               fo.OrderDate <= endDateTime &&
                               fo.Status != "已作废"
                         orderby fo.OrderDate, fo.Ref, fod.Seq
                         select new Contract.飞机总表()
                         {
                             单据号 = fo.Ref.Trim(),
                             类型 = "飞机票",
                             出票日期 = fo.ProvideTicketDate,
                             Customer = fo.Customer.Name.Trim(),
                             TempID = fod.ID,
                             SourceID = fo.ID,
                             Price = fod.Price,
                             Tax1 = fod.Tax1,
                             Tax2 = fod.Tax2,
                             Profit = fod.Profit,
                             NetPrice = fod.Profit,
                             VendorDiscount = fod.VendorDiscount,
                             Intergral = fod.Intergral,
                             IsInternational = fo.IsInternational,
                             CreditTerm = fo.CreditTerm.Trim(),
                             Vendor = fod.Vendor.Name.Trim(),
                             状态 = fo.CustomerStatus.Trim(),
                         }).ToList();

            list.AddRange(list1);


            var list2 = (from fc in entities.FlightChange
                         join fod in entities.FlightOrderDetail on fc.FlightOrderDetailID equals fod.ID
                         join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                         where fc.OrderDate >= startDateTime &&
                               fc.OrderDate <= endDateTime &&
                               fo.Status != "已作废"
                         orderby fo.OrderDate, fo.Ref, fod.Seq
                         select new Contract.飞机总表()
                         {
                             单据号 = fo.Ref.Trim(),
                             类型 = "飞机票改票",
                             出票日期 = fo.ProvideTicketDate,
                             Customer = fo.Customer.Name.Trim(),
                             TempID = fod.ID,
                             SourceID = fc.ID,
                             Price = fc.Price,
                             Tax1 = 0,
                             Tax2 = 0,
                             Profit = fc.Profit,
                             NetPrice = 0,
                             VendorDiscount = 0,
                             Intergral = 0,
                             IsInternational = fo.IsInternational,
                             CreditTerm = fo.CreditTerm.Trim(),
                             Vendor = fod.Vendor.Name.Trim(),
                             状态 = fc.CustomerStatus.Trim(),
                         }).ToList();

            list.AddRange(list2);

            var list3 = (from fr in entities.FlightReturn
                         join fod in entities.FlightOrderDetail on fr.FlightOrderDetailID equals fod.ID
                         join fo in entities.FlightOrder on fod.FlightOrderID equals fo.ID
                         where fr.OrderDate >= startDateTime &&
                               fr.OrderDate <= endDateTime &&
                               fo.Status != "已作废"
                         orderby fo.OrderDate, fo.Ref, fod.Seq
                         select new Contract.飞机总表()
                         {
                             单据号 = fo.Ref.Trim(),
                             类型 = "飞机票退票",
                             出票日期 = fo.ProvideTicketDate,
                             Customer = fo.Customer.Name.Trim(),
                             TempID = fod.ID,
                             SourceID = fr.ID,
                             Price = fr.CustomerReturnPrice,
                             Tax1 = 0,
                             Tax2 = 0,
                             Profit = fr.Profit,
                             NetPrice = 0,
                             VendorDiscount = 0,
                             Intergral = 0 - fr.Intergral,
                             IsInternational = fo.IsInternational,
                             CreditTerm = fo.CreditTerm.Trim(),
                             Vendor = fod.Vendor.Name.Trim(),
                             状态 = fr.CustomerStatus.Trim(),
                         }).ToList();

            list.AddRange(list3);

            foreach (Contract.飞机总表 _飞机总表 in list)
            {
                var tempList = (from fw in entities.FlightWay
                                where fw.FlightOrderDetailID == _飞机总表.TempID
                                orderby fw.Seq
                                select new
                                {
                                    Start = fw.DepartLocation,
                                    End = fw.Destination,
                                    Date = fw.DepartDate,
                                }).ToList();

                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < tempList.Count; i++)
                {
                    sb.Append(string.Format("{0} - ", tempList[i].Start));
                }
                sb.Append(tempList[tempList.Count - 1].End);

                _飞机总表.行程 = sb.ToString();
                _飞机总表.出发日期 = tempList[0].Date;
                if (tempList.Count == 1)
                    _飞机总表.WayType = "单程";
                else if (tempList.Count == 2)
                    _飞机总表.WayType = "双程";
                else
                    _飞机总表.WayType = "联程";

                var tempList1 = (from cs in entities.CustomerSettlement
                                 where cs.Type == _飞机总表.类型 &&
                                       cs.SourceID == _飞机总表.SourceID
                                 select cs).ToList();
                StringBuilder sb1 = new StringBuilder();
                foreach (var cs in tempList1)
                    if (cs.BankAccount != null)
                        sb1.Append(string.Format("{0} ", cs.BankAccount.Name.Trim()));
                _飞机总表.BankAccount = sb1.ToString();
            }

            return (from f in list
                    orderby f.出票日期, f.单据号
                    select f).ToList();
        }

    }
}
